package com.googlecode.bip.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import javax.naming.NamingException;
import com.googlecode.bip.dao.UserValueDAO;
import com.googlecode.bip.entity.IQuestion;
import com.googlecode.bip.entity.IUser;
import com.googlecode.bip.entity.IUserValue;
import com.googlecode.bip.entity.IllegalValueException;
import com.googlecode.bip.entity.impl.EntityHelper;
import com.googlecode.bip.entity.impl.UserValue;
import cx.ath.choisnet.sql.SQLTools;

/**
 *
 * @author Claude CHOISNET
 */
public class MySQLUserValueDAO
    extends MySQLDAOBuilder<IUserValue>
        implements UserValueDAO
{
    private static final long serialVersionUID = 1L;

    @Override //Listable<IQuestion>
    public IUserValue newInstance(final ResultSet rSet )
        throws SQLException
    {
        return UserValue.newInstance( rSet );
    }

    @Override
    public IUserValue findUserValue(
           final int          iduser,
           final int          idquestion,
           final Timestamp    userdate
           )
       throws NamingException, SQLException
   {
//       final String sql = "SELECT * FROM `uservaluesview` WHERE `iduser`="
//           + iduser
//           + " AND `idquestion`="
//           + idquestion
//           + " AND `userdate`='"
//           + EntityHelper.getNormalizedTimestamp( userdate )
//           +"';";
//
//       return getElement( sql );

       final String sql = "SELECT * FROM `uservaluesview` WHERE `iduser`=? AND `idquestion`=? AND `userdate`=?;";

       Connection connection = getConnection();
       IUserValue result     = null;

       try {
           PreparedStatement preparedStatement = connection.prepareStatement( sql );

           // Workaround: bad encoding of Timestamp to String
           preparedStatement.setInt( 1, iduser );
           preparedStatement.setInt( 2, idquestion );
           preparedStatement.setTimestamp( 3, EntityHelper.getNormalizedTimestamp( userdate ) );

           ResultSet resultSet  = preparedStatement.executeQuery();
           boolean   found      = resultSet.first();

           if( found ) {
               result = newInstance( resultSet );
               }

           preparedStatement.close();
           }
       finally {
           closeConnection( connection );
           }

       return result;
    }

    @Override
    public IUserValue getUserValue(
            final int   iduser,
            final int   idquestion,
            Timestamp   userdate
            )
        throws NamingException, SQLException
    {
        // normalized userdate
        userdate = EntityHelper.getNormalizedTimestamp( userdate );

        IUserValue uv = findUserValue( iduser, idquestion, userdate );

        if( uv == null ) {
            uv = new UserValue( iduser, idquestion, userdate );
            }

        return uv;
    }

    @Override
    public IUserValue getUserValue(
            final IUser     iuser,
            final IQuestion iquestion,
            final Timestamp userdate
            )
        throws NamingException, SQLException
    {
        return getUserValue( iuser.getIdUser(), iquestion.getIdQuestion(), userdate );
    }

    @Override
    public IUserValue insertUserValue(
            final IUser     user,
            final IQuestion question,
            final Timestamp userdate,
            final int       value,
            final String    comment,
            final boolean   isPrivate
            )
        throws NamingException, SQLException, IllegalValueException
    {
       final StringBuilder sql = new StringBuilder();

       // Check value
       if( value < question.getMinValue() ) {
           throw new IllegalValueException( value, question.getMinValue(), question.getMaxValue() );
           }
       if( value > question.getMaxValue() ) {
           throw new IllegalValueException( value, question.getMinValue(), question.getMaxValue() );
           }

       sql.append( "CALL add_uservalue( " )
          .append( user.getIdUser() )
          .append( ", " )
          .append( question.getIdQuestion() )
          .append( ", '" )
          .append( EntityHelper.getNormalizedTimestamp( userdate ) )
          .append( "', " )
          .append( value )
          .append( ", " );

        if( comment == null ) {
            sql.append( "NULL");
            }
        else {
            sql.append( "'" )
               .append( SQLTools.parseFieldValue( comment ) )
               .append( "'" );
            }

        sql.append( ", " )
           .append( isPrivate )
           .append( " );" );

       return executeQuery( sql.toString() );
    }

    @Override
    @Deprecated
    public IUserValue insertUserValue(
            IUser       user,
            IQuestion   question,
            Timestamp   date,
            int         value,
            String      comment
            )
        throws NamingException, SQLException, IllegalValueException
    {
        return insertUserValue( user, question, date, value, comment, false );
    }

    @Override
    public boolean deleteUserValue( final IUserValue uservalue )
        throws NamingException, SQLException
    {
        return deleteUserValue(
                uservalue.getIdUser(),
                uservalue.getIdQuestion(),
                uservalue.getUserDate()
                );
    }

    @Override
    public boolean deleteUserValue(
        final int        iduser,
        final int        idquestion,
        final Timestamp  userdate
        )
        throws NamingException, SQLException
    {
        final String sql = "CALL `delete_uservalue`( "
            + iduser
            + ", "
            + idquestion
            + ", '"
            + EntityHelper.getNormalizedTimestamp( userdate )
            + "' );";

        doUpdate( sql );

        return true;
    }

    @Override
    public boolean updateUserValue( final IUserValue uservalue )
        throws NamingException, SQLException
    {
        String        comment = uservalue.getUserComment();
        StringBuilder sql     = new StringBuilder();

        sql.append( "CALL add_or_update_uservalue( " )
           .append( Integer.toString( uservalue.getIdUser() ) )
           .append( ", " )
           .append(  uservalue.getIdQuestion() )
           .append( ", '" )
           .append( uservalue.getUserDate() )
           .append( "', " )
           .append( uservalue.getUserValue() )
           .append( ", ");

        if( comment == null ) {
            sql.append( "NULL");
            }
        else {
            sql.append( "'" )
               .append( SQLTools.parseFieldValue( comment ) )
               .append( "'" );
            }

        sql.append( ", " )
        .append( uservalue.isPrivate() )
        .append( " );" );

       doUpdate( sql.toString() );

       return true;
    }

}


